﻿
CREATE PROCEDURE [dbo].[ToolIDsByKeyword]
	@keywords varchar(100), 
	@script bit,
	@files bit,
	@notes bit,
	@allauthor bit,
	@author authortabletype readonly,
	@alltags bit,
	@tags tagtabletype readonly

AS
BEGIN

	SET NOCOUNT ON;

	SELECT tool.toolid FROM tool 
		WHERE (freetext (([Title],[Description]),@keywords) or @keywords='""') AND
			  (@allauthor=1 OR Author IN (select author from @author)) AND
			  (@alltags=1 OR tool.toolid IN (SELECT toolid FROM tooltag WHERE tag IN (select tag from @tags)))

	UNION

	SELECT  tool.toolid
		FROM    tool
			INNER JOIN toolscript ON tool.toolid = toolscript.toolid
		WHERE   (freetext (( [script], [scriptnote]), @keywords ) or @keywords='""')
			AND @script = 1
			AND (@allauthor=1 OR Author IN (select author from @author))
			AND (@alltags=1 OR tool.toolid IN (SELECT toolid FROM tooltag WHERE tag IN (select tag from @tags)))	
	
	UNION
    
	SELECT  tool.toolid
		FROM    tool
				INNER JOIN toolnote ON tool.toolid = toolnote.toolid
		WHERE   (freetext (([note]), @keywords ) or @keywords='""')
				AND @notes = 1	
				AND (@allauthor=1 OR Author IN (select author from @author))
				AND (@alltags=1 OR tool.toolid IN (SELECT toolid FROM tooltag WHERE tag IN (select tag from @tags)))	

	UNION 

	SELECT  tool.toolid
		FROM    tool
				INNER JOIN toolnote ON tool.toolid = toolnote.toolid
		WHERE   (freetext (([note]), @keywords ) or @keywords='""')
				AND @notes = 1	
				AND (@allauthor=1 OR Author IN (select author from @author))	
				AND (@alltags=1 OR tool.toolid IN (SELECT toolid FROM tooltag WHERE tag IN (select tag from @tags)))

	UNION

	SELECT tool.toolid from toolfilemap 
		inner join tool on tool.toolid = toolfilemap.toolid
		where stream_id in (select stream_id 
		FROM dbo.toolfile
		WHERE (freetext (([name],[file_stream]), @keywords )) or @keywords='""')
				AND (@allauthor=1 OR Author IN (select author from @author))	
				AND (@alltags=1 OR tool.toolid IN (SELECT toolid FROM tooltag WHERE tag IN (select tag from @tags)))


END